import time
import openpyxl
import pymysql

config = {
    'host': '101.201.117.5',
    'port': 3306,
    'user': 'root',
    'password': 'insur132',
    'db': 'insur_wallet',
    'cursorclass': pymysql.cursors.DictCursor,
}
connection = pymysql.connect(**config)
cursor = connection.cursor()


def fetchone(sql, *args):
    cursor.execute(sql, args)
    return cursor.fetchone()


def fetchall(sql, *args):
    cursor.execute(sql, args)
    return cursor.fetchall()


def writeToExcel(data):
    """将数据保存到Excel中"""
    if not isinstance(data, dict): raise Exception("参数必须是一个dict")
    wb = openpyxl.Workbook()

    for i, val in enumerate(data.items()):
        if len(val) != 2: raise Exception("格式不正确")
        if 0 == i:
            sheet = wb.active
            sheet.title = val[0]
        else:
            sheet = wb.create_sheet(val[0])

        for i in range(len(val[1])):
            if i == 0:
                # 字段名字
                for j, v in enumerate(val[1][i].keys()):
                    sheet.cell(row = 1, column = j+1, value=str(v))

            for j, v in enumerate(val[1][i].values()):
                sheet.cell(row=i+2, column=j+1, value=str(v))

    wb.save(time.strftime("%Y%m%d") + '.xlsx')


data = {'转入总次数': [], '转出总次数': [], '转入总额': [], '转出总额': []}
basicsData = fetchall("SELECT user_id,telephone, insur_cnt, reg_ts  FROM account  LIMIT %s", 10)
for i, item in enumerate(basicsData):
    tmp1 = {}; tmp2 = {}; tmp3 = {}; tmp4 = {}
    res = fetchone("SELECT count(from_user_id) as from_num FROM transfer WHERE from_user_id = %s", item.get('user_id'))
    tmp1['转出次数'] = res.get('from_num')
    data['转出总次数'].append({**item, **tmp1})
    # del(item['转出次数'])

    res = fetchone("SELECT count(to_user_id) as to_num FROM transfer WHERE to_user_id = %s", item.get('user_id'))
    tmp2['转入次数'] = res.get('to_num')
    data['转入总次数'].append({**item, **tmp2})
    # del (item['转入次数'])

    res = fetchone("SELECT sum(amount) as from_amount FROM transfer WHERE from_user_id = %s", item.get('user_id'))
    tmp3['转出总额'] = res.get('from_amount')
    data['转出总额'].append({**item, **tmp3})
    # del (item['转出总额'])

    res = fetchone("SELECT sum(amount) as to_amount FROM transfer WHERE to_user_id = %s", item.get('user_id'))
    tmp4['转入总额'] = res.get('to_amount')
    data['转入总额'].append({**item, **tmp4})




writeToExcel(data)
